from sqlalchemy import (create_engine, Column, Integer, String, Numeric,
                        DateTime, ForeignKey, Text, Boolean, Enum, event)
from sqlalchemy.inspection import inspect
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Mapper
from datetime import datetime
from common import config, utils

Base = declarative_base()
engine = create_engine(config.DB_STRING)

@event.listens_for(Mapper, "before_insert")
def remove_microseconds(mapper, connection, target):
    for key, value in vars(target).items():
        if isinstance(value, datetime):
            setattr(target, key, value.replace(microsecond=0))

class BaseModel(Base):
    __abstract__ = True
    def to_dict(self, include_relationships=True, only_relationships=None, max_depth=2, _current_depth=0, visited=None):
        """
        Args:
            include_relationships (bool): 是否包含所有 relationships
            only_relationships (list[str] or None): 只包含指定 relationships
            max_depth (int or None): 最大递归深度（0=只当前对象, 1=加一层relationship，以此类推）
            _current_depth (int): 当前递归的深度
            visited (set): 防止递归死循环
        """
        if visited is None:
            visited = set()

        obj_id = id(self)

        if obj_id in visited:
            # 遇到重复对象，只返回基本字段，不展开
            result = {}
            mapper = inspect(self.__class__)
            for column in mapper.columns:
                result[column.key] = getattr(self, column.key)
            return result

        visited.add(obj_id)

        result = {}
        mapper = inspect(self.__class__)

        # 普通字段
        for column in mapper.columns:
            result[column.key] = getattr(self, column.key)

        # 深度控制
        if max_depth is not None and _current_depth >= max_depth:
            return result  # 达到最大深度了，只返回基本字段

        if include_relationships or only_relationships:
            for name, relation in mapper.relationships.items():
                if only_relationships and name not in only_relationships:
                    continue

                value = getattr(self, name)

                if value is None:
                    result[name] = None
                elif relation.uselist:
                    result[name] = [
                        item.to_dict(
                            include_relationships=include_relationships,
                            only_relationships=only_relationships,
                            max_depth=max_depth,
                            _current_depth=_current_depth + 1,
                            visited=visited,
                        ) for item in value
                    ]
                else:
                    result[name] = value.to_dict(
                        include_relationships=include_relationships,
                        only_relationships=only_relationships,
                        max_depth=max_depth,
                        _current_depth=_current_depth + 1,
                        visited=visited,
                    )

        return result

class Category(BaseModel):
    "商品分类"
    __tablename__ = 'categories'

    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    parent_id = Column(Integer, nullable=True)
    created_at = Column(DateTime, default=datetime.now())

    products = relationship('Product', back_populates='category')
class Product(BaseModel):
    "商品"
    __tablename__ = 'products'
    
    id = Column(Integer, primary_key=True)
    code = Column(String(50), unique=True, nullable=False)
    name = Column(String(100), nullable=False)
    category_id = Column(Integer, ForeignKey("categories.id"), nullable=False)
    image = Column(String(255), nullable=True)
    unit = Column(String(20), nullable=False)
    price = Column(Numeric(10, 2), nullable=False)
    stock = Column(Integer, nullable=False)
    lowest_stock = Column(Integer, nullable=False, default=0) # 最低库存
    description = Column(Text, nullable=True)
    status = Column(Boolean, default=True) # 出售中、已下架
    created_at = Column(DateTime, default=datetime.now())
    user_id = Column(Integer) # 创建人ID
    updated_at = Column(DateTime, default=datetime.now())

    category = relationship('Category', back_populates='products')
    purchaseItems = relationship('PurchaseItem', back_populates='product')
    saleItems = relationship('SaleItem', back_populates='product')
    productSuppliers = relationship('ProductSupplier', back_populates='product')

class Supplier(BaseModel):
    "供应商"
    __tablename__ = 'suppliers'

    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    contact = Column(String(50), nullable=False)
    phone = Column(String(20), nullable=False)
    wechat = Column(String(50), nullable=True)
    address = Column(String(100), nullable=False)
    created_at = Column(DateTime, default=datetime.now())

    purchases = relationship('Purchase', back_populates='supplier')
    supplierProducts = relationship('ProductSupplier', back_populates='supplier')

class ProductSupplier(BaseModel):
    __tablename__ = 'product_suppliers'

    id = Column(Integer, primary_key=True)
    product_id = Column(Integer, ForeignKey('products.id'), nullable=False)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), nullable=False)
    cost_price = Column(Numeric(10, 2), nullable=False)
    created_at = Column(DateTime, default=datetime.now())

    product = relationship('Product', back_populates='productSuppliers')
    supplier = relationship('Supplier', back_populates='supplierProducts')

class Purchase(BaseModel):
    __tablename__ = 'purchases'
    
    id = Column(Integer, primary_key=True)
    code = Column(String(50), unique=True, nullable=False)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), nullable=False)
    status = Column(Enum(utils.PurchaseStatus))  # 待入库、已取消、已入库
    created_at = Column(DateTime, default=datetime.now())
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False) # 创建人ID
    total_price = Column(Numeric(10, 2), nullable=False) # 总金额
    updated_at = Column(DateTime, default=datetime.now())

    items = relationship('PurchaseItem', back_populates='purchase')
    supplier = relationship('Supplier', back_populates='purchases')
    user = relationship('User', back_populates='purchases')
    purchaseReturns = relationship('PurchaseReturn', back_populates='purchase')

class PurchaseItem(BaseModel):
    __tablename__ = 'purchase_items'
    
    id = Column(Integer, primary_key=True)
    purchase_id = Column(Integer, ForeignKey('purchases.id'), nullable=False)
    product_id = Column(Integer, ForeignKey('products.id'), nullable=False)
    quantity = Column(Integer, nullable=False)
    unit_price = Column(Numeric(10, 2), nullable=False)
    total_price = Column(Numeric(10, 2), nullable=False)
    status = Column(Boolean, default=True)  # 正常、退货

    purchase = relationship('Purchase', back_populates='items')
    product = relationship('Product', back_populates='purchaseItems')
    purchaseReturnItems = relationship('PurchaseReturnItem', back_populates='purchaseItem')

class PurchaseReturn(BaseModel):
    __tablename__ = 'purchase_returns'

    id = Column(Integer, primary_key=True)
    purchase_id = Column(Integer, ForeignKey('purchases.id'), nullable=False)
    return_date = Column(DateTime, default=datetime.now())
    reason = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.now())
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False) # 创建人ID
    total_price = Column(Numeric(10, 2), nullable=False)

    items = relationship('PurchaseReturnItem', back_populates='purchaseReturn')
    user = relationship('User', back_populates='purchaseReturns')
    purchase = relationship('Purchase', back_populates='purchaseReturns')

class PurchaseReturnItem(BaseModel):
    __tablename__ = 'purchase_return_items'

    id = Column(Integer, primary_key=True)
    purchase_return_id = Column(Integer, ForeignKey('purchase_returns.id'), nullable=False)
    purchase_item_id = Column(Integer, ForeignKey('purchase_items.id'), nullable=False)
    quantity = Column(Integer, nullable=False)
    unit_price = Column(Numeric(10, 2), nullable=False)
    total_price = Column(Numeric(10, 2), nullable=False)

    purchaseReturn = relationship('PurchaseReturn', back_populates='items')
    purchaseItem = relationship('PurchaseItem', back_populates='purchaseReturnItems')

class Sale(BaseModel):
    __tablename__ = 'sales'
    
    id = Column(Integer, primary_key=True)
    code = Column(String(50), unique=True, nullable=False)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=True)
    sale_date = Column(DateTime, default=datetime.now())
    status = Column(Enum(utils.SaleStatus), nullable=False)  # 待付款、已取消、已付款
    payment_method = Column(Enum(utils.PaymentMethod), nullable=True)
    created_at = Column(DateTime, default=datetime.now())
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False) # 创建人ID
    total_price = Column(Numeric(10, 2), nullable=False)
    updated_at = Column(DateTime, default=datetime.now())
    
    items = relationship('SaleItem', back_populates='sale')
    user = relationship('User', back_populates='sales')
    customer = relationship('Customer', back_populates='sales')
    saleReturns = relationship('SaleReturn', back_populates='sale')

class SaleItem(BaseModel):
    __tablename__ = 'sale_items'
    
    id = Column(Integer, primary_key=True)
    sale_id = Column(Integer, ForeignKey('sales.id'), nullable=False)
    product_id = Column(Integer, ForeignKey('products.id'), nullable=False)
    quantity = Column(Integer, nullable=False)
    unit_price = Column(Numeric(10, 2), nullable=False)
    total_price = Column(Numeric(10, 2), nullable=False)
    status = Column(Boolean, default=True)  # 正常、退货
    
    sale = relationship('Sale', back_populates='items')
    product = relationship('Product', back_populates='saleItems')
    saleReturnItems = relationship('SaleReturnItem', back_populates='saleItem')

class SaleReturn(BaseModel):
    __tablename__ = 'sale_returns'

    id = Column(Integer, primary_key=True)
    sale_id = Column(Integer, ForeignKey('sales.id'), nullable=False)
    return_date = Column(DateTime, default=datetime.now())
    reason = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.now())
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False) # 创建人ID
    total_price = Column(Numeric(10, 2), nullable=False)

    items = relationship('SaleReturnItem', back_populates='saleReturn')
    user = relationship('User', back_populates='saleReturns')
    sale = relationship('Sale', back_populates='saleReturns')

class SaleReturnItem(BaseModel):
    __tablename__ ='sale_return_items'

    id = Column(Integer, primary_key=True)
    sale_return_id = Column(Integer, ForeignKey('sale_returns.id'), nullable=False)
    sale_item_id = Column(Integer, ForeignKey('sale_items.id'), nullable=False)
    quantity = Column(Integer, nullable=False)
    unit_price = Column(Numeric(10, 2), nullable=False)
    total_price = Column(Numeric(10, 2), nullable=False)

    saleReturn = relationship('SaleReturn', back_populates='items')
    saleItem = relationship('SaleItem', back_populates='saleReturnItems')

class User(BaseModel):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), nullable=False)
    password = Column(String(100), nullable=False)
    role = Column(Enum(utils.UserRole), nullable=False)
    status = Column(Boolean, default=True)  # 1: 启用, 0: 禁用
    created_at = Column(DateTime, default=datetime.now())
    last_login_at = Column(DateTime, nullable=True)

    purchases = relationship('Purchase', back_populates='user')
    sales = relationship('Sale', back_populates='user')
    purchaseReturns = relationship('PurchaseReturn', back_populates='user')
    saleReturns = relationship('SaleReturn', back_populates='user')

class Customer(BaseModel):
    __tablename__ = 'customers'

    id = Column(Integer, primary_key=True)
    contact = Column(String(50), nullable=False)
    phone = Column(String(20), nullable=False)
    wechat = Column(String(50), nullable=True)
    created_at = Column(DateTime, default=datetime.now())

    sales = relationship('Sale', back_populates='customer')

class Log(BaseModel):
    __tablename__ = 'logs'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, nullable=False)
    details = Column(Text, nullable=False)
    created_at = Column(DateTime, default=datetime.now())

    log_relations = relationship('LogRelation', back_populates='log')

class LogRelation(BaseModel):
    __tablename__ = 'log_relations'

    id = Column(Integer, primary_key=True)
    log_id = Column(Integer, ForeignKey('logs.id'), nullable=False)
    table_name = Column(String(50), nullable=False)
    record_id = Column(Integer, nullable=False)

    log = relationship('Log', back_populates='log_relations')

class PurchaseBatch(BaseModel):
    __tablename__ = 'purchase_batches'

    id = Column(Integer, primary_key=True)
    purchase_item_id = Column(Integer, ForeignKey('purchase_items.id'), nullable=True)
    quantity_total = Column(Integer, nullable=False)
    quantity_available = Column(Integer, nullable=False)
    cost_price = Column(Numeric(10, 2), nullable=False)
    created_at = Column(DateTime, default=datetime.now())
    
    purchase_item = relationship('PurchaseItem')

class SaleItemBatch(BaseModel):
    __tablename__ = 'sale_item_batches'

    id = Column(Integer, primary_key=True)
    sale_item_id = Column(Integer, ForeignKey('sale_items.id'), nullable=False)
    purchase_batch_id = Column(Integer, ForeignKey('purchase_batches.id'), nullable=False)
    quantity = Column(Integer, nullable=False)
    cost_price = Column(Numeric(10, 2), nullable=False)
    total_cost = Column(Numeric(10, 2), nullable=False)

    sale_item = relationship('SaleItem')
    purchase_batch = relationship('PurchaseBatch')

# 创建数据库引擎
def initDB():
    Base.metadata.create_all(engine)